/**
 * 
 */
package rer.persistencia;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import rer.model.Aluno;
import rer.model.Matricula;
import rer.util.ConexaoJDBC_mysql;

/**
 * @author rickserrat
 *
 */
public class MatriculaDAO implements IMatriculaDAO {

	private Statement statement;
	private PreparedStatement preparedStatement;
	private ResultSet resultSet;
	private String query;
	private Matricula matricula;
	private List<Matricula> matriculas;
	private Connection conexao = ConexaoJDBC_mysql.getConnection();
	/* (non-Javadoc)
	 * @see rer.persistencia.IMatriculaDAO#cadastrar(rer.model.Matricula)
	 */
	@Override
	public Boolean cadastrar(Matricula matricula) {
		// TODO Auto-generated method stub
		
		try{
			
			query = "insert into db_ceirr.matricula (dataMatricula, Aluno_idAluno, Planos_idPlan, Turmas_idTurma, statusMat) "
					+ "values (CURDATE(), ?, ?, ?, 'P')";
			
			preparedStatement = conexao.prepareStatement(query);
			preparedStatement.setInt(1, matricula.getIdAluno());
			preparedStatement.setInt(2, matricula.getIdPlan());
			preparedStatement.setInt(3, matricula.getIdTurma());
			
			//l
			preparedStatement.execute();
			
			
			try {
				query ="UPDATE `aluno` SET `status`='A' WHERE idAluno = ?";
				
				
				preparedStatement = conexao.prepareStatement(query);
				
				preparedStatement.setInt(1,  matricula.getIdAluno());
				
				preparedStatement.executeUpdate();
				
				System.out.println("Status do aluno foi alterado para A(Ativo)! Data:" + matricula.getDataMatricula());		
				
				
			} catch (SQLException e) {
				// TODO: handle exception
				System.out.println(e.getMessage());
			}
			
		}catch(SQLException erro){
			System.out.println(erro.getMessage());
		}finally {
			
			try{
				preparedStatement.close();
				//System.out.println("\nStatement fechado");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				System.out.println("\n Erro ao fechar o objeto statement.");
			}
		}
		
		return false;
	}
	/* (non-Javadoc)
	 * @see rer.persistencia.IMatriculaDAO#editar(rer.model.Matricula)
	 */
	@Override
	public Boolean editar(Matricula matricula) {
		// TODO Auto-generated method stub
		try{
			
				query = "UPDATE `matricula` SET `dataMatricula`=?,`Aluno_idAluno`=?,"
						+ "`Planos_idPlan`=?,`Turmas_idTurma`=?,`statusMat`=? WHERE idTMatricula = ?";
	
				preparedStatement = conexao.prepareStatement(query);
				preparedStatement.setDate(1, new Date(matricula.getDataMatricula().getTime()));
				preparedStatement.setInt(2, matricula.getIdAluno());
				preparedStatement.setInt(3, matricula.getIdPlan());
				preparedStatement.setInt(4, matricula.getIdTurma());
				preparedStatement.setString(4, matricula.getStatus());
				preparedStatement.setInt(5, matricula.getIdMatricula());
				
				preparedStatement.executeUpdate();
				
				System.out.println("Dados tarefa Alterados com sucesso - DAO " + matricula.getIdMatricula() );
				return true;
				
			}catch(SQLException erro){
				System.out.println(erro.getMessage());
			}
			
			return false;
		}
	/* (non-Javadoc)
	 * @see rer.persistencia.IMatriculaDAO#deletar(int)
	 */
	@Override
	public Boolean deletar(int id) {
		// TODO Auto-generated method stub
		try{
			
			query = "update db_ceirr.matricula set statusMat = 'I' where idMatricula = ?";
			
			preparedStatement = conexao.prepareStatement(query);
			preparedStatement.setInt(1, id);
			
			preparedStatement.executeUpdate();
			
			System.out.println("\nMatricula apagada com sucesso");
			
			return true;
			
		}catch(SQLException erro){
			System.out.println(erro.getMessage());
			
			return false;
		}
	}
	/* (non-Javadoc)
	 * @see rer.persistencia.IMatriculaDAO#listar()
	 */
	@Override
	public List<Matricula> listar() {
		// TODO Auto-generated method stub
		matriculas = null;
		
		try{
			
			query = "SELECT * FROM db_ceirr.matricula where statusMat ='A'";
			statement = conexao.createStatement();
			resultSet = statement.executeQuery(query);
			
			matriculas = new ArrayList<Matricula>();
			
			while (resultSet.next()){
				
				matricula = new Matricula();
				matricula.setIdMatricula(resultSet.getInt("idMatricula"));
				matricula.setDataMatricula(resultSet.getDate("dataMatricula"));
				matricula.setIdAluno(resultSet.getInt("Aluno_idAluno"));
				matricula.setIdPlan(resultSet.getInt("Planos_idPlan"));
				matricula.setIdTurma(resultSet.getInt("Turmas_idTurma"));
				matricula.setStatus(resultSet.getString("statusMat"));
				
				matriculas.add(matricula);
				
			}
			
		}catch(SQLException erro){
			System.out.println(erro.getMessage());
		}
		
		try {
			statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("\n Erro ao fechar o objeto preparedStatement.");
		}
		System.out.println("\nSucesso pesquisa");
		return matriculas;
	}
	
	/* (non-Javadoc)
	 * @see rer.persistencia.IMatriculaDAO#pesquisarMatricula(int)
	 */
	@Override
	public Matricula pesquisarMatricula(int id) {
		// TODO Auto-generated method stub
		matricula = null;
		
		try{
			
			query = "SELECT * FROM db_ceirr.tarefas WHERE tarefas.idtarefas = ?";
			preparedStatement = conexao.prepareStatement(query);
			
			preparedStatement.setInt(1, id);
			
			resultSet = preparedStatement.executeQuery();
			
			if (resultSet.next()){
				
				matricula = new Matricula();
				matricula.setIdMatricula(resultSet.getInt("idMatricula"));
				matricula.setDataMatricula(resultSet.getDate("dataMatricula"));
				matricula.setIdAluno(resultSet.getInt("idAluno"));
				matricula.setIdPlan(resultSet.getInt("idPlan"));
				matricula.setIdTurma(resultSet.getInt("idTurma"));
				matricula.setStatus(resultSet.getString("status"));
								
			}
			
			return matricula;
			
		}catch(SQLException erro){
			System.out.println(erro.getMessage());
		} finally{
		
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				System.out.println("\n Erro ao fechar o objeto preparedStatement.");
			}
		}
		
		System.out.println("\nSucesso pesquisa - Matricula");
		
		return null;
	}
	
	
	@Override
	public List<Matricula> matriculaInsereServicos() {
		
		matriculas = null;
		
		try{

			query = "SELECT * FROM db_ceirr.matricula where statusMat = 'P' ORDER BY `idMatricula` DESC";
			statement = conexao.createStatement();
			resultSet = statement.executeQuery(query);
			
			matriculas = new ArrayList<Matricula>();
			
			while (resultSet.next()){
				
				matricula = new Matricula();
				matricula.setIdMatricula(resultSet.getInt("idMatricula"));
				matricula.setDataMatricula(resultSet.getDate("dataMatricula"));
				matricula.setIdAluno(resultSet.getInt("Aluno_idAluno"));
				matricula.setIdPlan(resultSet.getInt("Planos_idPlan"));
				matricula.setIdTurma(resultSet.getInt("Turmas_idTurma"));
				matricula.setStatus(resultSet.getString("statusMat"));
				
				matriculas.add(matricula);
				
			}
			
		}catch(SQLException erro){
			System.out.println(erro.getMessage());
		}
		
		try {
			statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("\n Erro ao fechar o objeto preparedStatement.");
		}
		System.out.println("\nSucesso pesquisa");
		return matriculas;
		
	}
	
	
}
